<#assign StringUtils = beansWrapperFn.getStaticModels()["org.apache.commons.lang3.StringUtils"]>
WITH W_USER_GROUP_ROLE AS
  (SELECT T.*,
  row_number() over(order by T_USER_GROUP.ORDER_NUM, T_USER_GROUP.code, T_ROLE.CODE) rn ,
  T_USER_GROUP.CODE AS USER_GROUP_CODE,
  T_USER_GROUP.NAME AS USER_GROUP_NAME,
  T_ROLE.CODE       AS ROLE_CODE,
  T_ROLE.NAME       AS ROLE_NAME
FROM C1_US_GROUP_ROLE T
LEFT JOIN C1_USER_GROUP T_USER_GROUP
ON (T_USER_GROUP.id = t.user_group_id)
LEFT JOIN C1_ROLE T_ROLE
ON (T_ROLE.id = t.ROLE_ID)
WHERE 1 = 1
<#if StringUtils.isNotBlank(userGroupCode)>
  AND (instr(T_USER_GROUP.code, :userGroupCode) > 0 OR instr(T_USER_GROUP.name, :userGroupCode)   > 0)
</#if>
<#if StringUtils.isNotBlank(roleCode)>
	and (instr(T_ROLE.code, :roleCode) > 0 or instr(T_ROLE.name, :roleCode) > 0)
</#if>
  )
SELECT T.*
    FROM W_USER_GROUP_ROLE T
WHERE t.rn BETWEEN <#if (begin_row_num??)>:begin_row_num<#else>1</#if> AND <#if (end_row_num??)>:end_row_num<#else>20</#if>